package top.majuan.study.study.dao;

import top.majuan.study.study.entity.Result;
import top.majuan.study.study.entity.TbDept;
import top.majuan.study.study.entity.TbEmployee;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TbEmployeeDAO {
    public static final String INSERT = "insert into tb_employee(dept_id,username,sex,salary) values(?,?,?,?)";
    public static final String QUERY = "select a.*,b.dept_name,b.description from tb_employee a inner join tb_dept b on a.dept_id = b.id";

    public static final String QUERY_BY_ID = "select * from tb_employee where id = ?";
    public static final String DELETE_BY_DEPT_ID = "delete from tb_employee where dept_id = ?";

    public static int deleteById(Integer deptid) throws Exception{
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(DELETE_BY_DEPT_ID);
        ps.setInt(1, deptid);
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    public static TbEmployee queryById(Integer id) throws Exception{
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_BY_ID);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        TbEmployee employee = null;
        if (rs.next()) {
            employee = new TbEmployee();
            employee.setId(rs.getInt("id"));
            employee.setDept_id(rs.getInt("dept_id"));
            employee.setUsername(rs.getString("username"));
            employee.setSex(rs.getString("sex"));
            employee.setSalary(rs.getLong("salary"));
            employee.setCreate_time(rs.getTimestamp("create_time"));
            //管理员工所在部门
            TbDept dept = TbDeptDAO.queryById(employee.getDept_id());
            employee.setDept(dept);
        }

        conn.close();
        return employee;
    }

    public static List<TbEmployee> query() throws Exception{
        Connection conn = DBHelper.getConnection();
        List<TbEmployee> list = new ArrayList<>();
        PreparedStatement ps = conn.prepareStatement(QUERY);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            TbEmployee employee = new TbEmployee();
            employee.setId(rs.getInt("id"));
            employee.setDept_id(rs.getInt("dept_id"));
            employee.setUsername(rs.getString("username"));
            employee.setSex(rs.getString("sex"));
            employee.setSalary(rs.getLong("salary"));
            employee.setCreate_time(rs.getTimestamp("create_time"));
//            添加部门关系数据
            TbDept dept = new TbDept();
            dept.setDept_name(rs.getString("dept_name"));
            dept.setDescription(rs.getString("description"));
            employee.setDept(dept);

            list.add(employee);
        }


        conn.close();
        return list;
    }
    public static int insert(TbEmployee employee) throws Exception{
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(INSERT);
        ps.setInt(1,employee.getDept_id());
        ps.setString(2,employee.getUsername());
        ps.setString(3,employee.getSex());
        ps.setLong(4,employee.getSalary());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }


}
